﻿CREATE FUNCTION PaymentSchedule(
	@LoanAmount numeric(38,10),
	@Payment numeric(38,10),
	@FirstPaymentDate datetime,
	@NumberOfPayments int,
	@PaymentsPerYear numeric(38,10),
	@InterestRate numeric(38,10),
	@PaymentStart int,
	@PaymentEnd int
) RETURNS @AmrtTbl TABLE(PaymentNumber int IDENTITY,Mos int,Date datetime,ToInterest numeric(18,2),ToPrinciple numeric(18,2),
		RemainingPrinciple numeric(18,2),FinanceCharge numeric(18,2),MinBalanceToRoll numeric(18,2)) AS
BEGIN

DECLARE @Mos int SET @Mos = 1
DECLARE @ToInterest numeric(38,2),@ToPrinciple numeric(38,2),@FinanceCharge numeric(38,2)

IF ISNULL(@PaymentStart,0) <= 0 SET @PaymentStart = 1
IF ISNULL(@PaymentEnd,0) <= 0 SET @PaymentEnd = 1000000
IF @InterestRate > 1 SET @InterestRate = @InterestRate / 100.0000
DECLARE @Date datetime SET @Date = dbo.Today(@FirstPaymentDate)
WHILE @Mos <= @NumberOfPayments BEGIN
	SET @ToInterest = @LoanAmount*@InterestRate/@PaymentsPerYear
	SET @FinanceCharge = ISNULL(@FinanceCharge,0) + @ToInterest
	SET @ToPrinciple = @Payment - @ToInterest
	SET @LoanAmount = @LoanAmount - @ToPrinciple
	IF @Mos >= @PaymentStart AND @Mos <= @PaymentEnd
		INSERT INTO @AmrtTbl VALUES(@Mos/@PaymentsPerYear*12,
			@Date,@ToInterest,@ToPrinciple,@LoanAmount,@FinanceCharge,@LoanAmount+@ToPrinciple*.35)
	SET @Date = 
		CASE @PaymentsPerYear
			 WHEN 12 THEN DATEADD(mm,@Mos,@FirstPaymentDate)
			 WHEN 24 THEN DATEADD(dd,(@Mos%2)*15,DATEADD(mm,@Mos/2,@FirstPaymentDate))
			 WHEN 26 THEN DATEADD(wk,2*@Mos,@FirstPaymentDate)
			 WHEN 52 THEN DATEADD(wk,1*@Mos,@FirstPaymentDate)
		END
	SET @Mos = @Mos + 1
END

RETURN
END



